<?php
/**
 * Created by PhpStorm.
 * User: wei
 * Date: 2016/6/7
 * Time: 17:25
 */
namespace Admin\Controller;
use Think\Controller;

class MoneyManageController extends CommonController
{
    protected function _initialize()
    {
        parent::_initialize();
        $this->breadcrumb1 = '财务管理';
        $this->breadcrumb2 = '钱包管理';
        $this->url = U('MoneyManage/index');
        $this->sqlname ='moneymanage'. session('user_auth')['uid'];
        $this->bsqlname ='balance'. session('user_auth')['uid'];
        $this->msqlname ='repairMoney'. session('user_auth')['uid'];
        $this->rsqlname ='repairFoenus'. session('user_auth')['uid'];
    }

    //主页
    public function index(){
        $master_id=I('post.master_id');
        $master_name=I('post.master_name');

        if($master_id){
            $where['u.phone']=$master_id;
        }

        if($master_name){
            $where['ifo.id_name']=array('like',"%$master_name%");
        }
        $where['u.is_real']=1;
        //构建sql语句
        $sql=M('user_money um')->join('left join zsf_user_real_info ifo on um.user_id=ifo.user_id')
            ->join('left join zsf_user u on u.user_id=um.user_id')
            ->field('u.user_id,ifo.id_name,u.phone,um.balance,um.repair_money,um.repair_bond,um.repair_foenus')
            ->where($where);
        //判断是否带条件查询
        if(!empty($where)){
            $sql=$sql->where($where);
        }

        $sql=$sql->select(false);
        $redis = S(array('type' => 'redis'));
        S($this->sqlname, $sql);
        //查询数据列表
        $list=M('user_money um')->join('left join zsf_user_real_info ifo on um.user_id=ifo.user_id')
            ->join('left join zsf_user u on u.user_id=um.user_id')
            ->field('u.user_id,ifo.id_name,u.phone,um.balance,um.repair_money,um.repair_bond,um.repair_foenus');

        //判断是否带条件查询
        if(!empty($where)){
            $list=$list->where($where);
        }
        $list=$list->select();

        //总条数
        $count=M('user_money um')->join('left join zsf_user_real_info ifo on um.user_id=ifo.user_id')
            ->join('left join zsf_user u on u.user_id=um.user_id')
            ->field('count(*) sum');

        if(!empty($where)){
            $count=$count->where($where);
        }
        $count=$count->find();
        $Page       = new \Think\Page($count['sum'],25);
        $show       = $Page->show();

        $this->assign('list',$list);
        $this->assign('page',$show);
        $this->display();
    }

    //余额列表
    public function balance(){
        $master_id=I('get.master_id');
        $date1=I('post.time1');
        $date2=I('post.time2');
        $where['h.id']=array('gt',0);
        $where['h.user_id']=$master_id;
        $where['h.type']=4;

        //判断是否带有筛选条件
        if(!empty($date1) || !empty($date2)){
            $time1=strtotime($date1);
            $time2=strtotime($date2);
            $where['h.add_time']=array('between',array($time1,$time2));
        }

        $sql=M('money_history h')
            ->join('left join zsf_user_real_info ifo on ifo.user_id=h.user_id')
            ->where($where)
            ->field('h.user_id,h.add_time,h.remark,h.total,h.is_plus,h.balance,ifo.id_name')
            ->order('h.add_time desc')
            ->select(false);
        $redis = S(array('type' => 'redis'));
        S($this->bsqlname, $sql);
        $list=M('money_history h')
            ->join('left join zsf_user_real_info ifo on ifo.user_id=h.user_id')
            ->where($where)
            ->field('h.user_id,h.add_time,h.remark,h.total,h.is_plus,h.balance,ifo.id_name')
            ->order('h.add_time desc')
            ->select();
        $msg=M('money_history h')
            ->join('left join zsf_user_real_info ifo on ifo.user_id=h.user_id')
            ->where($where)
            ->field('h.user_id,h.add_time,h.remark,h.total,h.is_plus,h.balance,ifo.id_name')
            ->order('h.add_time desc')
            ->find();
        $this->assign('list',$list);
        $this->assign('msg',$msg);
        $this->display();
    }

    //维修金列表
    public function repairMoney(){
        $master_id=I('get.master_id');
        $date1=I('post.time1');
        $date2=I('post.time2');
        //判断是否带有筛选条件
        if(!empty($date1) && !empty($date2)){
            $time1=strtotime($date1);
            $time2=strtotime($date2);
            $where['p.add_time']=array('between',array($time1,$time2));
        }
        //拼凑条件
        $where['p.user_id']=$master_id;
        $where['p.m_isready']=0;
        $where['p.status']=0;

        $sql=M('repair_order_protocol p')->join('left join zsf_user_real_info u on u.user_id=p.user_id')
            ->where($where)
            ->field('p.order_id,u.id_name,p.donetime,p.maintain')
            ->select(false);
        //查询语句
        $list=M('repair_order_protocol p')->join('left join zsf_user_real_info u on u.user_id=p.user_id')
            ->where($where)
            ->field('p.order_id,u.id_name,p.donetime,p.maintain')
            ->select();
        $redis = S(array('type' => 'redis'));
        S($this->msqlname, $sql);
        //统计总钱
        $total=M('repair_order_protocol p')->join('left join zsf_user_real_info u on u.user_id=p.user_id')
            ->where($where)
            ->field('sum(p.maintain) total,u.id_name,u.user_id')
            ->find();

        $this->assign('list',$list);
        $this->assign('id',$master_id);
        $this->assign('total',$total);
        $this->display();
    }


    //保证金列表
    public function repairBond(){
        $master_id=I('get.master_id');
        $date1=I('post.time1');
        $date2=I('post.time2');
        //判断是否有筛选条件
        if(!empty($date1) && !empty($date2)){
            $time1=strtotime($date1);
            $time2=strtotime($date2);
            $where['p.add_time']=array('between',array($time1,$time2));
        }
        //拼凑条件
        $where['p.m_isready']=0;
        $where['p.status']=0;
        $where['p.user_id']=$master_id;

        //查询语句
        $count=M('repair_order_protocol p')->join('left join zsf_user_real_info ifo on ifo.user_id=p.user_id')
            ->where($where)
            ->field('p.order_id,ifo.id_name,p.donetime,p.bond')
            ->count();
        $Page       = new \Think\Page($count,25);
        //查询语句
        $list=M('repair_order_protocol p')->join('left join zsf_user_real_info ifo on ifo.user_id=p.user_id')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->field('p.order_id,ifo.id_name,p.donetime,p.bond')
            ->select();
        $sql=M('repair_order_protocol p')->join('left join zsf_user_real_info ifo on ifo.user_id=p.user_id')
            ->where($where)
            ->field('p.order_id,ifo.id_name,p.donetime,p.bond')
            ->select(false);
        $redis = S(array('type' => 'redis'));
        $sql=S($this->rsqlname,$sql);

        $total=M('repair_order_protocol p')->join('left join zsf_user_real_info ifo on ifo.user_id=p.user_id')
            ->where('p.m_isready=0')
            ->field('sum(p.bond) total,ifo.id_name')
            ->find();
        $show       = $Page->show();
        $this->assign('page',$show);
        $this->assign('list',$list);
        $this->assign('total',$total);
        $this->display();
    }


    //保证金利息
    public function repairFoenus(){
        $id=I('get.master_id');
        $date1=I('post.date1');
        $date2=I('post.date2');
        $rate_id=I('post.rate_id');
        if($date1 && $date2){
            $time1=strtotime($date1);
            $time2=strtotime($date2);
            $where['r.add_time']=array('gt',$time1);
            $where['r.add_time']=array('lt',$time2);
        }

        if($rate_id){
            $where['r.id']=$rate_id;
        }

        $where['u.user_id']=$id;
        $count=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->where($where)
            ->count();

        $Page       = new \Think\Page($count,25);
        $list=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->field('u.phone,ifo.id_name,p.bond,r.money,r.id,r.add_time')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();
        //记录sql
        $sql=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->field('u.phone,ifo.id_name,p.bond,r.money,r.id,r.add_time')
            ->where($where)
            ->select(false);
        $redis = S(array('type' => 'redis'));
        $sql=S($this->rsqlname,$sql);

        $total=M('repair_order ro')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_id')
            ->join('left join zsf_ratemanage r on r.user_id=ro.worker_user_id')
            ->where('ro.pay_time>0')
            ->field('sum(p.bond) b_total,sum(r.money) m_total')
            ->select();

        $show       = $Page->show();
        $this->assign('detail',$list);
        $this->assign('total',$total);
        $this->assign('page',$show);// 赋值分页输出
        $this->display();
    }

    //导出excel
    public function excelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->sqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'师傅账号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'师傅名称','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'钱包余额','size'=>'20'),
            'E'=>array('index'=>'E1','name'=>'维修金','size'=>'20'),
            'F'=>array('index'=>'F1','name'=>'保证金','size'=>'20'),
            'G'=>array('index'=>'G1','name'=>'保证金利息','size'=>'20'),
        );
        //设置标题
        $title='钱包管理';
        //选择类型
        $type=8;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }

    //余额导出
    public function bexcelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->bsqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'资金来源','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'进账时间','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'来源金额','size'=>'20'),
            'E'=>array('index'=>'E1','name'=>'钱包余额','size'=>'20'),
        );
        //设置标题
        $title='钱包余额管理';
        //选择类型
        $type=11;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }


    //维修金导出excel
    public function mexcelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->msqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'维修订单编号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'维修金期限','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'维修金额','size'=>'20'),
        );
        //设置标题
        $title='维修金钱包管理';
        //选择类型
        $type=12;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }


    //保证金导出excel
    public function boexcelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->bosqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'维保订单编号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'维保金期限','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'维保金额','size'=>'20'),
        );
        //设置标题
        $title='维保金钱包管理';
        //选择类型
        $type=13;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }


    //利息导出excel
    public function rexcelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->rsqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'利息编号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'保证金(元)','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'利息(元)','size'=>'20'),
            'E'=>array('index'=>'E1','name'=>'产生时间','size'=>'20'),
        );
        //设置标题
        $title='利息钱包管理';
        //选择类型
        $type=13;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }

}